CREATE TABLE `dws_waybill_customer_complaint_agg_new_dt`
(
    `data_time`                  date NULL COMMENT "数据ETL日期",
    `proxy_code`                 varchar(200) NULL COMMENT "代理区编码",
    `regional_code`              varchar(200) NULL COMMENT "大区编码",
    `regional_desc`              varchar(200) NULL COMMENT "大区名称",
    `proxy_name`                 varchar(200) NULL COMMENT "代理区名称",
    `work_cnt`                   bigint(20) NULL COMMENT "呼叫中心客诉量",
    `work_total`                 bigint(20) NULL COMMENT "呼叫中心客诉量汇总",
    `project_cnt`                bigint(20) NULL COMMENT "平台客诉量",
    `project_total`              bigint(20) NULL COMMENT "平台客诉量汇总",
    `appeal_cnt`                 bigint(20) NULL COMMENT "邮政客诉量",
    `appeal_total`               bigint(20) NULL COMMENT "邮政客诉量汇总",
    `claim_cnt`                  bigint(20) NULL COMMENT "理赔客诉量",
    `claim_total`                bigint(20) NULL COMMENT "理赔客户量汇总",
    `third_platform_cnt`         bigint(20) NULL COMMENT "第三方客诉量",
    `third_platform_ratio`       decimal(10, 2) NULL COMMENT "第三方客诉率",
    `third_platform_total`       bigint(20) NULL COMMENT "第三方客诉量汇总",
    `third_platform_total_ratio` decimal(10, 2) NULL COMMENT "第三方汇总客诉率",
    `transfer_cnt`               bigint(20) NULL COMMENT "转运客诉量",
    `transfer_cnt_ratio`         decimal(10, 2) NULL COMMENT "转运客诉率",
    `transfer_total`             bigint(20) NULL COMMENT "转运客诉量-全网",
    `transfer_total_ratio`       decimal(10, 2) NULL COMMENT "转运客诉率-全网",
    `network_cnt`                bigint(20) NULL COMMENT "网点客诉量",
    `network_total`              bigint(20) NULL COMMENT "网点累计客诉量",
    `network_ratio`              decimal(10, 2) NULL COMMENT "网点客诉率",
    `network_total_ratio`        decimal(10, 2) NULL COMMENT "网点累计客诉率",
    `all_cnt`                    bigint(20) NULL COMMENT "全网客诉量",
    `all_ratio`                  decimal(10, 2) NULL COMMENT "全网客诉率",
    `all_total`                  bigint(20) NULL COMMENT "全网客诉量汇总",
    `all_total_ratio`            decimal(10, 2) NULL COMMENT "全网客诉率汇总",
    `taking_cnt`                 bigint(20) NULL COMMENT "代理区出件量",
    `arrival_cnt`                bigint(20) NULL COMMENT "代理区到件量"
) ENGINE=OLAP
DUPLICATE KEY(`data_time`, `proxy_code`, `regional_code`)
COMMENT "客诉量汇总报表"
PARTITION BY RANGE(`data_time`)
DISTRIBUTED BY HASH(`proxy_code`) BUCKETS 5
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "5",
"in_memory" = "false",
"storage_format" = "V2"
);

CREATE TABLE `dws_waybill_customer_complaint_agg_mt`
(
    `data_time`                  date NULL COMMENT "数据日期-yyyy-MM-dd",
    `regional_code`              varchar(200) NULL COMMENT "大区编码",
    `proxy_code`                 varchar(200) NULL COMMENT "代理区code",
    `regional_desc`              varchar(200) NULL COMMENT "大区name",
    `proxy_name`                 varchar(200) NULL COMMENT "代理区name",
    `proxy_cnt_month`            bigint(20) NULL COMMENT "当月工单量汇总-代理区",
    `proxy_total_month`          bigint(20) NULL COMMENT "月累计客诉量-全网",
    `proxy_cnt_month_ratio`      decimal(10, 2) NULL COMMENT "月累计客诉率-代理区",
    `proxy_total_month_ratio`    decimal(10, 2) NULL COMMENT "月累计客诉率-全网",
    `network_cnt_month`          bigint(20) NULL COMMENT "月累计网点客诉量-代理区",
    `network_total_month`        bigint(20) NULL COMMENT "月累计网点客诉量-全网",
    `network_cnt_month_ratio`    decimal(10, 2) NULL COMMENT "月累计网点客诉率-代理区",
    `network_total_month_ratio`  decimal(10, 2) NULL COMMENT "月累计网点客诉率-全网",
    `third_platform_cnt_month`   bigint(20) NULL COMMENT "月累计三方客诉量-代理区",
    `third_platform_total_month` bigint(20) NULL COMMENT "月累计三方客诉量-代理区",
    `third_cnt_month_ratio`      decimal(10, 2) NULL COMMENT "月累计三方客诉率-代理区",
    `third_total_month_ratio`    decimal(10, 2) NULL COMMENT "月累计三方客诉率-全网",
    `transfer_cnt_month`         bigint(20) NULL COMMENT "月累计转单客诉率-代理区",
    `transfer_total_month`       bigint(20) NULL COMMENT "月累计转单客诉率-全网",
    `transfer_cnt_month_ratio`   decimal(10, 2) NULL COMMENT "月累计转单客诉率-代理区",
    `transfer_total_month_ratio` decimal(10, 2) NULL COMMENT "月累计转单客诉率-全网",
    `proxy_taking_cnt_month`     bigint(20) NULL COMMENT "月累计收件量-代理区",
    `proxy_arrival_cnt_month`    bigint(20) NULL COMMENT "月累计到件量-代理区",
    `proxy_work_cnt_month`       bigint(20) NULL COMMENT "当月代理区的普通工单客诉量",
    `proxy_project_cnt_month`    bigint(20) NULL COMMENT "当月代理区的普通工单客诉量",
    `proxy_claim_cnt_month`      bigint(20) NULL COMMENT "当月代理区的理赔工单客诉量",
    `proxy_appeal_cnt_month`     bigint(20) NULL COMMENT "当月代理区的邮政工单客诉量",
    `work_total_month`           bigint(20) NULL COMMENT "普通工单客诉量-月累计",
    `project_total_month`        bigint(20) NULL COMMENT "普通工单客诉量-月累计",
    `claim_total_month`          bigint(20) NULL COMMENT "理赔工单客诉量-月累计",
    `appeal_total_month`         bigint(20) NULL COMMENT "邮政工单客诉量-月累计",
    `proxy_taking_total_month`   bigint(20) NULL COMMENT "月累计收件量-全网",
    `proxy_arrival_total_month`  bigint(20) NULL COMMENT "月累计到件量-全网"
) ENGINE=OLAP
DUPLICATE KEY(`data_time`, `regional_code`, `proxy_code`)
COMMENT "客诉量月汇总报表"
PARTITION BY RANGE(`data_time`)
DISTRIBUTED BY HASH(`proxy_code`) BUCKETS 5
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-360",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "5",
"in_memory" = "false",
"storage_format" = "V2"
);